set doc off
set verify off
--
--    Program: CAPGLEXT_AUTOREV.sql
--    Description:
--       Extract auto reverse settings associated with the SOB that the user specified
--
--    Revision:
--       1st     
--    Parameter: 1. set of books short name e.g. SOB_C999

--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   cursor cur_s (p_ngo_sob varchar2) is
     select * 
     from   gl_autoreverse_options 
     where  set_of_books_id = (select set_of_books_id 
                               from   gl_sets_of_books 
                               where  upper(short_name) = upper(p_ngo_sob));

begin
   l_pathname := '/tmp';
   l_filename := 'CAPGL_AUTOREV.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
       
   for s in cur_s(l_ngo_sob) loop
     
     utl_file.put_line( l_utl_file, 
       S.SET_OF_BOOKS_ID         ||chr(9)||
       S.JE_CATEGORY_NAME        ||chr(9)||
       S.METHOD_CODE             ||chr(9)||
       S.REVERSAL_PERIOD_CODE    ||chr(9)||
       S.AUTOREVERSE_FLAG        ||chr(9)||
       S.AUTOPOST_REVERSAL_FLAG  ||chr(9)||
       to_char(S.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
       S.LAST_UPDATED_BY         ||chr(9)||
       to_char(S.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
       S.CREATED_BY              ||chr(9)||
       S.LAST_UPDATE_LOGIN       ||chr(9)||
       S.REVERSAL_DATE_CODE      ||chr(9)||
       S.ATTRIBUTE1              ||chr(9)||
       S.ATTRIBUTE2              ||chr(9)||
       S.ATTRIBUTE3              ||chr(9)||
       S.ATTRIBUTE4              ||chr(9)||
       S.ATTRIBUTE5              ||chr(9)||
       S.ATTRIBUTE6              ||chr(9)||
       S.ATTRIBUTE7              ||chr(9)||
       S.ATTRIBUTE8              ||chr(9)||
       S.ATTRIBUTE9              ||chr(9)||
       S.ATTRIBUTE10             ||chr(9)||
       S.ATTRIBUTE11             ||chr(9)||
       S.ATTRIBUTE12             ||chr(9)||
       S.ATTRIBUTE13             ||chr(9)||
       S.ATTRIBUTE14             ||chr(9)||
       S.ATTRIBUTE15             ||chr(9)||
       S.CONTEXT                 || chr(9) ||
       CAPBREXT_COMMON.GetUser(S.LAST_UPDATED_BY)|| chr(9) ||
       CAPBREXT_COMMON.GetUser(S.CREATED_BY)
     );
     
     l_processed_count := l_processed_count + 1;

   end loop;       
   
   if l_processed_count = 0 then
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := 'No Auto Reverse defined for this NGO '||l_ngo_sob;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPGLEXT_AUTOREV', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);  
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
